﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlCommand cmd = new SqlCommand("select [Date],[Time],[From],[To],Flight_Number,Aircraft,[Economy price],ROUND([Economy price]*1.35,0) as Business,ROUND(([Economy price]*1.35)*1.3,0) as FirstPrice,FlightStatus from FlightShedules",sqlConnection1);
            SqlDataAdapter adapt = new SqlDataAdapter(cmd);
            DataTable table = new DataTable();
            adapt.Fill(table);

            listView1.Items.Clear();
            foreach (DataRow row in table.Rows)
            {
                ListViewItem lvi = new ListViewItem();
                lvi.Text = row[0].ToString();
                for (int i = 1; i < row.ItemArray.Count(); i++)
                {
                    lvi.SubItems.Add(row[i].ToString());
                }
                if (bool.Parse(row["FlightStatus"].ToString())==false)
                {
                    lvi.BackColor = Color.Red;
                    lvi.ForeColor = Color.White;
                }

                listView1.Items.Add(lvi);
            }
            listView1.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (listView1.SelectedItems.Count==1)
            {
                string day = listView1.SelectedItems[0].SubItems[0].Text;
                string FN = listView1.SelectedItems[0].SubItems[4].Text;
                string status = bool.Parse(listView1.SelectedItems[0].SubItems[9].Text.Trim()) == true ? "0" : "1";
                //MessageBox.Show(listView1.SelectedItems[0].SubItems[9].Text.Trim());
                //MessageBox.Show(status);
                SqlCommand cmd = new SqlCommand("update FlightShedules set FlightStatus=@FS where [Date]=@day and [Flight_Number]=@FN", sqlConnection1);
                cmd.Parameters.AddWithValue("@FS", status);
                cmd.Parameters.AddWithValue("@day", day);
                cmd.Parameters.AddWithValue("@FN", FN);
                sqlConnection1.Open();
                cmd.ExecuteNonQuery();
                sqlConnection1.Close();
                button1_Click(sender, e);
            }
        }

        int R1 = 0;
        int R2 = 0;
        int R3 = 0;
        private void button2_Click(object sender, EventArgs e)
        {
            R1 = R2 = R2 = 0;
            string line;
            StreamReader streamReader = new StreamReader(textBox1.Text.Trim(), Encoding.UTF8);
            while ((line=streamReader.ReadLine())!=null)
            {
                string[] oneline = line.Split(',');
                string sqltext = "";
                bool ischongfu = false;

                if (oneline.Count()==9)
                {
                    if (oneline[0].ToString().ToUpper() == "ADD")
                    {
                        sqltext = "insert into FlightShedules([Date],[Time],[From],[To],[Flight_Number],[Aircraft],[Economy price],[FlightStatus],FlightID) values(@D,@time,@From,@To,@FN,@A,@EP,@FS,@Fid)";


                        ischongfu= isINDB(oneline[1], int.Parse(oneline[3]));
                    }
                    if (oneline[0].ToString().ToUpper() == "EDIT")
                    {
                        sqltext = "update FlightShedules set [Time]=@time,[From]=@From,[To]=@To,[Aircraft]=@A,[Economy price]=@EP,[FlightStatus]=@FS where [Date]=@D and [Flight_Number]=@FN";
                    }

                    SqlCommand cmd = new SqlCommand(sqltext, sqlConnection1);
                    try
                    {
                        cmd.Parameters.AddWithValue("@D", oneline[1]);
                        cmd.Parameters.AddWithValue("@time", oneline[2]);
                        cmd.Parameters.AddWithValue("@FN", oneline[3]);
                        cmd.Parameters.AddWithValue("@From", oneline[4]);
                        cmd.Parameters.AddWithValue("@To", oneline[5]);
                        cmd.Parameters.AddWithValue("@A", oneline[6]);
                        cmd.Parameters.AddWithValue("@EP", oneline[7]);
                        cmd.Parameters.AddWithValue("@FS", ctosql(oneline[8].Trim()));
                        //MessageBox.Show(ctosql(oneline[8].Trim()));
                        cmd.Parameters.AddWithValue("@Fid", getMaxID());
                    }
                    catch
                    {

                    }
                    sqlConnection1.Open();
                    try
                    {
                        
                        if (ischongfu == false)
                        {
                            cmd.ExecuteNonQuery();
                            R1++;
                        }
                        else
                        {
                            R2++;
                        }
                        
                        ischongfu = false;
                    }
                    catch (Exception ex)
                    {
                        R3++;
                        //MessageBox.Show(ex.Message);
                    }
                    sqlConnection1.Close();
                }
                else
                {
                    R3++;
                }
                
            }
            streamReader.Close();
            label1.Text = R1.ToString();
            label2.Text = R2.ToString();
            label3.Text = R3.ToString();
            button1_Click(sender, e);

        }

        public int getMaxID()
        {
            int result=0;
            SqlCommand cmd = new SqlCommand("select COUNT(1)+1 as c from FlightShedules", sqlConnection1);
            sqlConnection1.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                result = int.Parse(dr["c"].ToString());
            }
            sqlConnection1.Close();
            return result;
        }

        public bool isINDB(string day,int fn)
        {
            int result=1000;
            SqlCommand cmd = new SqlCommand("select COUNT(1) as c from FlightShedules where [date]=@date and [Flight_Number]=@FN", sqlConnection1);
            cmd.Parameters.AddWithValue("@date", day);
            cmd.Parameters.AddWithValue("@FN", fn);
            sqlConnection1.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr.Read())
            {
                result = int.Parse(dr["c"].ToString());
            }
            sqlConnection1.Close();
            if (result>0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public string ctosql(string str)
        {
            
            if (str.ToUpper().ToString()== "CANCELED")
            {
                return "0";
            }
            if (str.ToString().ToUpper()=="OK")
            {
                return "1";
            }
            return null;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            
        }
    }
}
